﻿Public Class frmTheoDoiDonHang
    Private IndexCurrentMonth As Integer = 0
    Private ReportType As Integer = 0
    Private Sub frmTheoDoiDonHang_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            DisableControl()
            dtpTheoNgay.Enabled = True
            Nam_Load()
            Thang_Load()
            'Report_TheoNgay()
        Catch ex As Exception

        End Try
    End Sub
#Region "Thống Kê Đơn Hàng"
    Private Sub RadioButton1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton1.CheckedChanged
        Select Case ReportType
            Case 0
                Report_TheoNgay()
            Case 1
                Report_TheoThang()
            Case 2
                Report_TheoNam()
            Case 3
                Report_TuNgayDenNgay()
            Case 4
                Report_TenKhachHang()
            Case 5
                Report_SoHoaDon()
            Case 6
                Report_MaSanPham()
        End Select
    End Sub
    '---tat ca
    Private Sub RadioButton3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton3.CheckedChanged
        Select Case ReportType
            Case 0
                Report_TheoNgay()
            Case 1
                Report_TheoThang()
            Case 2
                Report_TheoNam()
            Case 3
                Report_TuNgayDenNgay()
            Case 4
                Report_TenKhachHang()
            Case 5
                Report_SoHoaDon()
            Case 6
                Report_MaSanPham()

        End Select
    End Sub
    Private Sub RadioButton4_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadioButton4.CheckedChanged
        Select Case ReportType
            Case 0
                Report_TheoNgay()
            Case 1
                Report_TheoThang()
            Case 2
                Report_TheoNam()
            Case 3
                Report_TuNgayDenNgay()
            Case 4
                Report_TenKhachHang()
            Case 5
                Report_SoHoaDon()
            Case 6
                Report_MaSanPham()

        End Select
    End Sub
    Sub DisableControl()
        dtpTheoNgay.Enabled = False
        cbxTheoThang.Enabled = False
        cbxNamTheoThang.Enabled = False
        cbxTheoNam.Enabled = False
        dtpTuNgay.Enabled = False
        dtpDenNgay.Enabled = False
        txtSoHoaDon.Enabled = False
        txtTenSanPham.Enabled = False
    End Sub

    Private Sub rbtTheoNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNgay.CheckedChanged
        DisableControl()
        dtpTheoNgay.Enabled = True
        ReportType = 0
    End Sub
    Private IsPostBack As Boolean = False
    Private Sub rbtTheoThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoThang.CheckedChanged
        DisableControl()
        cbxTheoThang.Enabled = True
        cbxNamTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxTheoThang.SelectedIndex = IndexCurrentMonth
        End If
        cbxNamTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxNamTheoThang.SelectedIndex = 0
        End If
        ReportType = 1
        IsPostBack = True
    End Sub
    Private IsPostBack2 As Boolean = False
    Private Sub rbtTheoNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNam.CheckedChanged
        DisableControl()
        cbxTheoNam.Enabled = True
        If IsPostBack2 = False Then
            cbxTheoNam.SelectedIndex = 0
        End If
        ReportType = 2
        IsPostBack2 = True
    End Sub

    Private Sub rbtTuNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTuNgay.CheckedChanged
        DisableControl()
        dtpTuNgay.Enabled = True
        dtpDenNgay.Enabled = True
        ReportType = 3
    End Sub
    Private Sub rbtSoHoaDon_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtSoHoaDon.CheckedChanged
        DisableControl()
        txtSoHoaDon.Enabled = True
        ReportType = 5
    End Sub
    Private Sub rbtMaSanPham_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbtMaSanPham.CheckedChanged
        DisableControl()
        txtTenSanPham.Enabled = True
        ReportType = 6
    End Sub
    Public Sub KhachHang_Load()
        Select Case ReportType
            Case 0
                Report_TheoNgay()
            Case 1
                Report_TheoThang()
            Case 2
                Report_TheoNam()
            Case 3
                Report_TuNgayDenNgay()
            Case 4
                Report_TenKhachHang()
            Case 5
                Report_SoHoaDon()
            Case 6
                Report_MaSanPham()
        End Select
    End Sub
    Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
        Try
            lblUpdate.Visible = True
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
                Case 4
                    Report_TenKhachHang()
                Case 5
                    Report_SoHoaDon()
                Case 6
                    Report_MaSanPham()
            End Select
            lblUpdate.Visible = False
        Catch ex As Exception
            ShowDonHang(ex)
        End Try
    End Sub
#End Region
#Region "Load data"
    Private Sub Nam_Load()
        Dim currentYear As Integer = Now.Year
        cbxTheoNam.Items.Clear()
        Try
            While (currentYear >= 2011)
                cbxTheoNam.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                cbxNamTheoThang.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                currentYear = currentYear - 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Sub Thang_Load()
        Dim currentYear As Integer = 1
        cbxTheoThang.Items.Clear()
        Try
            Dim i As Integer = 0
            While (currentYear <= 12)
                cbxTheoThang.Items.Add(New MonthInfo(currentYear, currentYear.ToString))
                If currentYear = Now.Month Then
                    IndexCurrentMonth = i
                End If
                currentYear = currentYear + 1
                i = i + 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region
#Region "Sub Class"
    Private Class YearInfo
        Dim _Nam As Integer
        Dim _TenNam As String
        Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
            _Nam = Nam
            _TenNam = TenNam
        End Sub
        Public Function Nam() As Integer
            Return _Nam
        End Function
        Public Function TenNam() As String
            Return _TenNam
        End Function
        Public Overrides Function ToString() As String
            Return _TenNam
        End Function
    End Class
    Private Class MonthInfo
        Dim _Thang As Integer
        Dim _TenThang As String
        Public Sub New(ByVal Thang As Integer, ByVal TenThang As String)
            _Thang = Thang
            _TenThang = TenThang
        End Sub
        Public Function Thang() As Integer
            Return _Thang
        End Function
        Public Function TenThang() As String
            Return _TenThang
        End Function
        Public Overrides Function ToString() As String
            Return _TenThang
        End Function
    End Class
#End Region
#Region "Thống kê - báo cáo"
    '--------Tính tiền doanh thu của khách hàng trên 1 đơn hàng-------------------
    Private Function TinhDoanhThuKhachHangTheoDonHang(ByVal IdDonHangBan As Integer) As Single
        Try
            Dim sRet As Single = 0
            Try
                Dim dbConn As New OleDb.OleDbConnection
                Dim dataAccess As New DataAccess
                Dim dtThanhTien As New DataTable
                Dim query_Tien_HoaDon As String
                Dim adapter As OleDb.OleDbDataAdapter
                Dim TongTienHoaDon As Integer = 0
                query_Tien_HoaDon = String.Format("Select ThanhTien From ChiTietDonHangBan Where Xoa=0 And LoaiPhi='HangHoa' And IdDonHangBan={0} ", IdDonHangBan)
                dataAccess.ConnectDatabase_DBAccess(dbConn)
                adapter = New OleDb.OleDbDataAdapter(query_Tien_HoaDon, dbConn)
                adapter.Fill(dtThanhTien)
                For i As Integer = 0 To dtThanhTien.Rows.Count - 1
                    sRet = sRet + Single.Parse(dtThanhTien.Rows(i)("ThanhTien"))
                Next
                Return sRet
            Catch ex As Exception
                MessageBox.Show("Có lỗi trong quá trình xử lý.Vui lòng thử lại")
            End Try
        Catch ex As Exception

        End Try
    End Function
#End Region
#Region "Thống kê theo ngày"
    Sub Report_TheoNgay()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim NgayBan As String = dtpTheoNgay.Value.ToShortDateString
        Dim query As String = ""
        If RadioButton3.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And NgayBan='" & NgayBan & "'" & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton1.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Chưa duyệt' And NgayBan='" & NgayBan & "'" & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton2.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã duyệt' And NgayBan='" & NgayBan & "'" & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton4.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And  TrangThai=N'Đã hủy' And NgayBan='" & NgayBan & "'" & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        End If
        Dim dtThongKe As New DataTable
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtThongKe)
        dbConn.Close()
        dtThongKe.Columns.Add("DoanhThu")
        dtThongKe.Columns.Add("STT")
        Dim sThanhTien As String
        Dim DoanhThu As Single = 0
        For i As Integer = 0 To dtThongKe.Rows.Count - 1
            'Tính Doanh Thu Khách Hàng
            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            dtThongKe.Rows(i)("DoanhThu") = sThanhTien
            dtThongKe.Rows(i)("STT") = i + 1
            formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        Next
        dgvKhachHang.AutoGenerateColumns = False
        dgvKhachHang.DataSource = dtThongKe
        If dgvKhachHang.Rows.Count - 1 > 0 Then
            dgvKhachHang.Rows(0).Selected = True
        End If
    End Sub
#End Region
#Region "Report Theo Tháng"
    Private Sub Report_TheoThang()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim Thang As MonthInfo
        Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
        If Thang Is Nothing Then
            MessageBox.Show("Bạn chưa chọn tháng.Xin vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        Dim Nam As YearInfo
        Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
        Dim query As String = ""
        If RadioButton3.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And Thang='" & Thang.TenThang & "'" & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton1.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Chưa duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton2.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã duyệt' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton4.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã hủy' And Thang='" & Thang.TenThang & "' And Nam='" & Nam.TenNam & "' order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        End If
        Dim dtThongKe As New DataTable
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtThongKe)
        dbConn.Close()
        dtThongKe.Columns.Add("DoanhThu")
        dtThongKe.Columns.Add("STT")
        Dim sThanhTien As String
        Dim DoanhThu As Single = 0
        For i As Integer = 0 To dtThongKe.Rows.Count - 1
            'Tính Doanh Thu Khách Hàng
            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            dtThongKe.Rows(i)("DoanhThu") = sThanhTien
            dtThongKe.Rows(i)("STT") = i + 1
            formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        Next
        dgvKhachHang.AutoGenerateColumns = False
        dgvKhachHang.DataSource = dtThongKe
        If dgvKhachHang.Rows.Count - 1 > 0 Then
            dgvKhachHang.Rows(0).Selected = True
        End If
    End Sub
#End Region
#Region "Report theo năm"
    Sub Report_TheoNam()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        Dim Nam As YearInfo
        Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
        If Nam Is Nothing Then
            MessageBox.Show("Bạn chưa chọn năm.Xin vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If
        Dim query As String = ""
        If RadioButton3.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And Nam='" & Nam.TenNam & "'" & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton1.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Chưa duyệt' And Nam='" & Nam.TenNam & "'" & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton2.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã duyệt' And Nam='" & Nam.TenNam & "'" & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton4.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã hủy' And Nam='" & Nam.TenNam & "' order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        End If
        Dim dtThongKe As New DataTable
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtThongKe)
        dbConn.Close()
        dtThongKe.Columns.Add("DoanhThu")
        dtThongKe.Columns.Add("STT")
        Dim sThanhTien As String
        Dim DoanhThu As Single = 0
        For i As Integer = 0 To dtThongKe.Rows.Count - 1
            'Tính Doanh Thu Khách Hàng
            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            dtThongKe.Rows(i)("DoanhThu") = sThanhTien
            dtThongKe.Rows(i)("STT") = i + 1
            formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        Next
        dgvKhachHang.AutoGenerateColumns = False
        dgvKhachHang.DataSource = dtThongKe
        If dgvKhachHang.Rows.Count - 1 > 0 Then
            dgvKhachHang.Rows(0).Selected = True
        End If
    End Sub
#End Region
#Region "Report từ ngày đến ngày"
    Sub Report_TuNgayDenNgay()
        '2010007004  -->20100704
        Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
        Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
        If DenNgay < TuNgay Then
            Dim dlgRes As DialogResult
            dlgRes = MessageBox.Show( _
                      "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                      "Thông báo", _
                MessageBoxButtons.OK, _
                      MessageBoxIcon.Question)
            If dlgRes = Windows.Forms.DialogResult.OK Then
                Exit Sub
            End If
        End If
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess
        'Dim query As String = "Select IDKhachHang,TenKhachHang,SoDienThoai,DiaChi,NgayBan,TienDaThanhToan,TienGiamTru,ThanhToan,TongTien,SanPham,DonGia From KhachHang Where  IsHidden='" & cm.Encrypt("Hóa đơn hiển thị") & "' And   Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " Order by Ngay_So DESC"
        Dim query As String = ""
        If RadioButton3.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton1.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And Where TrangThai=N'Chưa duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton2.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And Where TrangThai=N'Đã duyệt' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        ElseIf RadioButton4.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã hủy' And Ngay_So <=" & DenNgay & " And Ngay_So >=" & TuNgay & " order by ngay_so desc, cast(so as int) desc,TrangThai ASC"
        End If
        Dim dtThongKe As New DataTable
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtThongKe)
        dbConn.Close()
        dtThongKe.Columns.Add("DoanhThu")
        dtThongKe.Columns.Add("STT")
        Dim sThanhTien As String
        Dim DoanhThu As Single = 0
        For i As Integer = 0 To dtThongKe.Rows.Count - 1
            'Tính Doanh Thu Khách Hàng
            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            dtThongKe.Rows(i)("DoanhThu") = sThanhTien
            dtThongKe.Rows(i)("STT") = i + 1
            formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        Next
        dgvKhachHang.AutoGenerateColumns = False
        dgvKhachHang.DataSource = dtThongKe
        If dgvKhachHang.Rows.Count - 1 > 0 Then
            dgvKhachHang.Rows(0).Selected = True
        End If
    End Sub
#End Region
#Region "Report theo tên khách hàng"
    REM Tìm kiếm tên khách hàng
    Private MaKhachHang As String
    'Sub KhachHang_Search()
    '    Try
    '        Dim dbConn As New OleDb.OleDbConnection
    '        Dim dataAccess As New DataAccess
    '        Dim dsKhachHang As New DataSet
    '        Dim dtKhachHang As New DataTable
    '        Dim query As String = "Select KhachHangId,MaKhachHang,TenKhachHang,SoDienThoai,DiaChi From DanhMucKhachHang Where Xoa=0 And TenKhachHang Like N'%" & txtTenKhachHang_Search.Text.Trim & "%'"
    '        Dim adapter As OleDb.OleDbDataAdapter
    '        dataAccess.ConnectDatabase_DBAccess(dbConn)
    '        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
    '        adapter.Fill(dsKhachHang)
    '        dbConn.Close()
    '        dtKhachHang = dsKhachHang.Tables(0)
    '        Panel_KhachHang_Search.Visible = True
    '        dgvKhachHang_Search.AutoGenerateColumns = False
    '        dgvKhachHang_Search.DataSource = dtKhachHang
    '        Panel_KhachHang_Search.BringToFront()
    '    Catch ex As Exception
    '        MessageBox.Show(ex.ToString())
    '    End Try
    'End Sub

    'Private Sub txtTenKhachHang_Search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
    '    Try
    '        If e.KeyCode = Keys.Enter Then
    '            txtTenKhachHang_Search.Text = dgvKhachHang_Search.Rows(dgvKhachHang_Search.CurrentCell.RowIndex).Cells("TenKhachHang_S").Value.ToString()
    '            Panel_KhachHang_Search.Visible = False
    '            cmdThongKe.Focus()
    '        End If
    '    Catch ex As Exception

    '    End Try
    'End Sub

    'Private Sub txtTenKhachHang_Search_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
    '    Try
    '        If txtTenKhachHang_Search.Text <> "" Then
    '            KhachHang_Search()
    '            If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
    '                dgvKhachHang_Search.Focus()
    '            End If
    '        Else
    '            Panel_KhachHang_Search.Visible = False
    '        End If
    '    Catch ex As Exception
    '        MessageBox.Show(ex.ToString())
    '    End Try
    'End Sub
    'Private Sub dgvKhachHang_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs)
    '    Try
    '        txtTenKhachHang_Search.Text = dgvKhachHang_Search.Rows(e.RowIndex).Cells("TenKhachHang_S").Value.ToString()
    '        MaKhachHang = dgvKhachHang_Search.Rows(e.RowIndex).Cells("KhachHangId_ThongKe").Value.ToString()
    '        Panel_KhachHang_Search.Visible = False
    '        cmdThongKe.Focus()
    '    Catch ex As Exception

    '    End Try
    'End Sub

    Private Sub cmdThongKe_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdThongKe.Enter
        Try
            'Panel_KhachHang_Search.Visible = False
        Catch ex As Exception

        End Try
    End Sub
    Sub Report_TenKhachHang()
        'Dim dbConn As New OleDb.OleDbConnection
        'Dim dataAccess As New DataAccess
        'REM end checking 
        'Dim query As String = ""
        'If RadioButton3.Checked Then
        '    query = "Select * from DonHangBan Where Xoa=0 And TenKhachHang=N'" & txtTenKhachHang_Search.Text.Trim & "'" & " order by ngay_so desc, cast(so as int) desc"
        'ElseIf RadioButton1.Checked Then
        '    query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Chưa duyệt' And TenKhachHang=N'" & txtTenKhachHang_Search.Text.Trim & "'" & " order by ngay_so desc, cast(so as int) desc"
        'ElseIf RadioButton2.Checked Then
        '    query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã duyệt' And TenKhachHang=N'" & txtTenKhachHang_Search.Text.Trim & "'" & " order by ngay_so desc, cast(so as int) desc"
        'End If
        'Dim dtThongKe As New DataTable
        'Dim adapter As OleDb.OleDbDataAdapter
        'dataAccess.ConnectDatabase_DBAccess(dbConn)
        'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        'adapter.Fill(dtThongKe)
        'dbConn.Close()
        'dtThongKe.Columns.Add("DoanhThu")
        'dtThongKe.Columns.Add("STT")
        'Dim sThanhTien As String
        'Dim DoanhThu As Single = 0
        'For i As Integer = 0 To dtThongKe.Rows.Count - 1
        '    'Tính Doanh Thu Khách Hàng
        '    DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
        '    sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
        '    dtThongKe.Rows(i)("DoanhThu") = sThanhTien
        '    dtThongKe.Rows(i)("STT") = i + 1
        '    formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        'Next
        'dgvKhachHang.AutoGenerateColumns = False
        'dgvKhachHang.DataSource = dtThongKe
        'If dgvKhachHang.Rows.Count - 1 > 0 Then
        '    dgvKhachHang.Rows(0).Selected = True
        'End If
    End Sub
    Sub Report_SoHoaDon()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess

        Dim query As String = ""
        If RadioButton3.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And So='" & txtSoHoaDon.Text.Trim & "'" & " order by ngay_so desc, cast(so as int) desc"
        ElseIf RadioButton1.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Chưa duyệt' And So='" & txtSoHoaDon.Text.Trim & "'" & " order by ngay_so desc, cast(so as int) desc"
        ElseIf RadioButton2.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã duyệt' And So='" & txtSoHoaDon.Text.Trim & "'" & " order by ngay_so desc, cast(so as int) desc"
        ElseIf RadioButton4.Checked Then
            query = "Select * from DonHangBan Where Xoa=0 And TrangThai=N'Đã hủy' And So='" & txtSoHoaDon.Text.Trim & "'" & " order by ngay_so desc, cast(so as int) desc"
        End If
        Dim dtThongKe As New DataTable
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtThongKe)
        dbConn.Close()
        dtThongKe.Columns.Add("DoanhThu")
        dtThongKe.Columns.Add("STT")
        Dim sThanhTien As String
        Dim DoanhThu As Single = 0
        For i As Integer = 0 To dtThongKe.Rows.Count - 1
            'Tính Doanh Thu Khách Hàng
            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            dtThongKe.Rows(i)("DoanhThu") = sThanhTien
            dtThongKe.Rows(i)("STT") = i + 1
            formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        Next
        dgvKhachHang.AutoGenerateColumns = False
        dgvKhachHang.DataSource = dtThongKe
        If dgvKhachHang.Rows.Count - 1 > 0 Then
            dgvKhachHang.Rows(0).Selected = True
        End If
    End Sub
    Sub Report_MaSanPham()
        Dim dbConn As New OleDb.OleDbConnection
        Dim dataAccess As New DataAccess

        Dim query As String = ""
        If RadioButton3.Checked Then
            query = "Select * from DonHangBan Where IDDonHangBan In (Select IDDonHangBan From ChiTietDonHangBan Where MaSanPham=N'" & txtTenSanPham.Text & "') And  Xoa=0 order by ngay_so desc, cast(so as int) desc"
        ElseIf RadioButton1.Checked Then
            query = "Select * from DonHangBan Where IDDonHangBan In (Select IDDonHangBan From ChiTietDonHangBan Where MaSanPham=N'" & txtTenSanPham.Text & "') And  Xoa=0 And TrangThai=N'Chưa duyệt' order by ngay_so desc, cast(so as int) desc"
        ElseIf RadioButton2.Checked Then
            query = "Select * from DonHangBan Where IDDonHangBan In (Select IDDonHangBan From ChiTietDonHangBan Where MaSanPham=N'" & txtTenSanPham.Text & "') And  Xoa=0 And TrangThai=N'Đã duyệt' order by ngay_so desc, cast(so as int) desc"
        ElseIf RadioButton4.Checked Then
            query = "Select * from DonHangBan Where IDDonHangBan In (Select IDDonHangBan From ChiTietDonHangBan Where MaSanPham=N'" & txtTenSanPham.Text & "') And Xoa=0 And TrangThai=N'Đã hủy' order by ngay_so desc, cast(so as int) desc"
        End If
        Dim dtThongKe As New DataTable
        Dim adapter As OleDb.OleDbDataAdapter
        dataAccess.ConnectDatabase_DBAccess(dbConn)
        adapter = New OleDb.OleDbDataAdapter(query, dbConn)
        adapter.Fill(dtThongKe)
        dbConn.Close()
        dtThongKe.Columns.Add("DoanhThu")
        dtThongKe.Columns.Add("STT")
        Dim sThanhTien As String
        Dim DoanhThu As Single = 0
        For i As Integer = 0 To dtThongKe.Rows.Count - 1
            'Tính Doanh Thu Khách Hàng
            DoanhThu = TinhDoanhThuKhachHangTheoDonHang(dtThongKe.Rows(i)("IdDonHangBan"))
            sThanhTien = CDec(DoanhThu.ToString).ToString("N", Globalization.CultureInfo.CreateSpecificCulture("vi-vn")).ToString()
            dtThongKe.Rows(i)("DoanhThu") = sThanhTien
            dtThongKe.Rows(i)("STT") = i + 1
            formatDate2VN(dtThongKe.Rows(i)("NgayBan"))
        Next
        dgvKhachHang.AutoGenerateColumns = False
        dgvKhachHang.DataSource = dtThongKe
        If dgvKhachHang.Rows.Count - 1 > 0 Then
            dgvKhachHang.Rows(0).Selected = True
        End If
    End Sub
#End Region
#Region "Search San pham"
    Private Sub SanPham_Search(ByVal KeyWord As String)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String = "SELECT * FROM (Select SanPham.DVTSanPhamId,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa From SanPham " & _
                                    " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                    " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                    " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                    " Where SanPham.Xoa=0 And SanPham.MaSanPham Like N'" & KeyWord & "%') AS SP WHERE SP.DVTSANPHAMID IS NOT NULL Order By TenSanPham"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham_Search.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                'chú giải
            Next

            dgvSanPham_Search.DataSource = dtSanPham
            If dgvSanPham_Search.Rows.Count - 1 > 0 Then
                dgvSanPham_Search.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTenSanPham_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                Dim TenSanPham As String
                Dim DVT As String
                TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                txtTenSanPham.Text = TenSanPham
                'hide search panel
                PanelSanPham_Search.Visible = False
                PanelSanPham_Search.Visible = False
            End If
        Catch ex As Exception

        End Try
    End Sub


    Private Sub txtTenSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyUp
        Try
            If txtTenSanPham.Text <> "" Then
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgvSanPham_Search.Focus()
                End If
            End If
            txtTenSanPham.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgvSanPham_Search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvSanPham_Search.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    PanelSanPham_Search.Visible = True
                Case Keys.Up
                    PanelSanPham_Search.Visible = True
                Case Keys.Enter
                    Dim TenSanPham As String
                    Dim DVT As String
                    TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                    DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                    txtTenSanPham.Text = TenSanPham
                    'hide search panel
                    PanelSanPham_Search.Visible = False
                    PanelSanPham_Search.Visible = False
                Case Else
                    txtTenSanPham.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub
    Private bEdit As Boolean
    Private Sub txtTenSanPham_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenSanPham.TextChanged
        Try
            If txtTenSanPham.Text <> "" Then
                'show search panel
                If Not bEdit Then
                    PanelSanPham_Search.Visible = True
                    SanPham_Search(txtTenSanPham.Text)
                Else
                    PanelSanPham_Search.Visible = False
                End If
            Else
                'hide search panel
                PanelSanPham_Search.Visible = False
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvSanPham_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham_Search.CellDoubleClick
        Try
            'Gan Du Lieu
            Dim TenSanPham As String
            Dim DVT As String
            TenSanPham = dgvSanPham_Search.Rows(e.RowIndex).Cells("MaSanPham").Value.ToString
            DVT = dgvSanPham_Search.Rows(e.RowIndex).Cells("TenDonViTinh").Value.ToString
            txtTenSanPham.Text = TenSanPham
            'hide search panel
            PanelSanPham_Search.Visible = False
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
    Private Sub cmdXemDonHang_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdXemDonHang.Click
        Try
            Dim IDDonHangBan As Integer = Integer.Parse(dgvKhachHang.Rows(dgvKhachHang.CurrentCell.RowIndex).Cells("IDDonHangBan").Value.ToString)
            frmXemDonHang.IDDonHangBan = IDDonHangBan
            frmXemDonHang.FormSender = "TheoDoiDonHang"
            ShowModalForm(frmXemDonHang, False, False)
            frmXemDonHang.IDDonHangBan = 0
        Catch ex As Exception

        End Try
    End Sub
End Class